package com.tt.fashionWebSite.dao.jpa;

import com.tt.fashionWebSite.dao.ChiTietHoaDonReportDao;
import com.tt.fashionWebSite.dao.GenericDao;
import com.tt.fashionWebSite.domain.ChiTietHoaDon;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.springframework.stereotype.Repository;

import javax.persistence.Query;
import java.util.Date;
import java.util.List;

/**
 * Created by isoft-work 5 on 1/15/2015.
 */
@Repository("chiTietHoaDonReport")
public class ChiTietHoaDonReportJpaDao extends GenericJpaDao<ChiTietHoaDon, String> implements ChiTietHoaDonReportDao {
   private static SessionFactory factory;
   public ChiTietHoaDonReportJpaDao () {
      super(ChiTietHoaDon.class);
   }

//   so luong san pham con lai
   @Override
   public  List<Object[]> getNumberRemainProduct() {
      Session session = (Session) getEntityManager().getDelegate();
      String strQuery = "SELECT "
            + "san_pham.MA_SP, TEN_SP, "
            + "SUM(SO_LUONG) "
            + "FROM chi_tiet_san_pham, san_pham "
            + "WHERE chi_tiet_san_pham.MA_SP = san_pham.MA_SP GROUP BY TEN_SP "
            + "ORDER BY SUM(SO_LUONG) DESC ";

      SQLQuery query = session.createSQLQuery(strQuery);

      List<Object[]> results = query.list();
      return results;
   }

   @Override
   public List<Object[]> getRevenueForDate(String date) {
      Session session = (Session) getEntityManager().getDelegate();
      String strQuery = "SELECT "
            + "san_pham.MA_SP, TEN_SP, "
            + "sum(chi_tiet_hd.SO_LUONG), "
            + "chi_tiet_hd.GIA_BAN, "
            + "sum(chi_tiet_hd.SO_LUONG * chi_tiet_hd.GIA_BAN) "
            + "FROM chi_tiet_hd, san_pham, chi_tiet_san_pham, hoa_don "
            + "WHERE chi_tiet_hd.MA_HD = hoa_don.MA_HD and chi_tiet_hd.MA_CT_SP = chi_tiet_san_pham.MA_CT_SP and chi_tiet_san_pham.MA_SP = san_pham.MA_SP AND hoa_don.TRANG_THAI = 2 AND hoa_don.NGAY_LAP_HD = '" + date + "'"
            + " group by san_pham.MA_SP, TEN_SP, chi_tiet_hd.GIA_BAN  "
            + "ORDER BY sum(chi_tiet_hd.SO_LUONG) DESC ";

      SQLQuery query = session.createSQLQuery(strQuery);

      List<Object[]> results = query.list();
      return results;
   }

   @Override
   public List<Object[]> getNumberSellProductForMonth(String month, String year) {
      Session session = (Session) getEntityManager().getDelegate();
      String strQuery = "SELECT "
            + "san_pham.MA_SP, TEN_SP, "
            + "sum(chi_tiet_hd.SO_LUONG), "
            + "chi_tiet_hd.GIA_BAN, "
            + "sum(chi_tiet_hd.SO_LUONG * chi_tiet_hd.GIA_BAN) "
            + "FROM chi_tiet_hd, san_pham, chi_tiet_san_pham, hoa_don "
            + "WHERE chi_tiet_hd.MA_HD = hoa_don.MA_HD and chi_tiet_hd.MA_CT_SP = chi_tiet_san_pham.MA_CT_SP and chi_tiet_san_pham.MA_SP = san_pham.MA_SP AND hoa_don.TRANG_THAI = 2 "
            + "AND MONTH(hoa_don.NGAY_LAP_HD) = " + month + " AND YEAR(hoa_don.NGAY_LAP_HD) = " + year
            + " group by san_pham.MA_SP, TEN_SP, chi_tiet_hd.GIA_BAN ";

      SQLQuery query = session.createSQLQuery(strQuery);

      List<Object[]> results = query.list();
      return results;
   }

   @Override
   public List<Object[]> getRevenueForMonth(String date) {
      Session session = (Session) getEntityManager().getDelegate();
      String strQuery = "SELECT "
            + "NGAY_LAP_HD, sum(TONG_TIEN) "
            + "FROM hoa_don "
            + "WHERE hoa_don.TRANG_THAI = 2 and NGAY_LAP_HD = '" + date + "'";

      SQLQuery query = session.createSQLQuery(strQuery);

      List<Object[]> results = query.list();
      return results;
   }

   @Override
   public List<Object[]> getRevenueForQuarterly(String month, String year) {
      Session session = (Session) getEntityManager().getDelegate();
      String strQuery = "SELECT "
            + "MONTH(NGAY_LAP_HD), sum(TONG_TIEN) "
            + "FROM hoa_don "
            + "WHERE hoa_don.TRANG_THAI = 2 and MONTH(NGAY_LAP_HD) = " + month + " AND YEAR (NGAY_LAP_HD) = " + year
            + " GROUP BY MONTH(NGAY_LAP_HD)";

      SQLQuery query = session.createSQLQuery(strQuery);

      List<Object[]> results = query.list();
      return results;
   }

   @Override
   public List<Object[]> getBuyOfUser(String month , String year) {
      Session session = (Session) getEntityManager().getDelegate();
      String strQuery = "SELECT "
            + "thong_tin_tv.MA_TV, thong_tin_tv.HO, thong_tin_tv.Ten, sum(TONG_TIEN) "
            + "FROM hoa_don, thong_tin_tv "
            + "WHERE thong_tin_tv.MA_TV = hoa_don.MA_TV and hoa_don.TRANG_THAI = 2 and MONTH(NGAY_LAP_HD) = " + month + " AND YEAR (NGAY_LAP_HD) = " + year
            + " group by thong_tin_tv.HO, thong_tin_tv.Ten ";

      SQLQuery query = session.createSQLQuery(strQuery);

      List<Object[]> results = query.list();
      return results;
   }

   @Override
   public List<Object[]> getNumberSaleProduct() {
      Session session = (Session) getEntityManager().getDelegate();
      String strQuery = "SELECT "
            + "san_pham.MA_SP, TEN_SP, NGAY_BD, NGAY_KT "
            + "FROM chi_tiet_km, chi_tiet_san_pham, san_pham, khuyen_mai "
            + "WHERE khuyen_mai.MA_KM = chi_tiet_km.MA_KM and chi_tiet_km.MA_CT_SP = chi_tiet_san_pham.MA_CT_SP and chi_tiet_san_pham.MA_SP = san_pham.MA_SP and NGAY_BD <= SYSDATE() and NGAY_KT >= SYSDATE() ";

      SQLQuery query = session.createSQLQuery(strQuery);

      List<Object[]> results = query.list();
      return results;
   }
}
